#-*- coding: utf-8 -*-
"""

 Alchemy Model

 $Id: model.py 450 2012-04-13 12:35:55Z andre $

"""

import re
from password import Password

from sqlalchemy.orm import relation, backref, validates
from sqlalchemy import Table, Column, Sequence, ForeignKey, ColumnDefault, UniqueConstraint
from sqlalchemy.types import Integer, BigInteger, Float, String, Boolean
from sqlalchemy.sql.functions import max as sql_max

from sqlalchemy.ext.declarative import declarative_base

import meta
from sqlbase import qbClass

__all__ = [ 'cmUser', 'cmDomain', 'cmAlias', 'cmGroup', 'cmConfiguration', 'cmQuota', 'cmDestination', 'cmUserGroup', 'cmVersion' ]

Base=declarative_base(metadata=meta.metadata)

class IncorrectUserParams(Exception):
    def __init__(self, value):
        self.value = value
    def __str__(self):
        return repr(self.value)

class cmUser(Base, qbClass):
    """
    cubemail User
    """
    __tablename__ = 'pd_users'
    __table_args__  = ( UniqueConstraint("username", "domain_id"), {} )

    id = Column(Integer, Sequence('users_id_seq'), primary_key=True)
    username = Column(String(255), nullable=False)
    password = Column(String(255))
    schema = Column(String(255), default='{PLAIN}', nullable=True)
    uid = Column(Integer, default=10000, nullable=False)
    gid = Column(Integer, default=8, nullable=False)
    mail = Column(String(255), default='maildir:~/maildir:INBOX=~/maildir/INBOX:LAYOUT=fs:INDEX=~/indexes/', nullable=False)
    domain_id = Column(Integer, ForeignKey('pd_domains.id'), nullable=False)
    name = Column(String(255))
    surname = Column(String(255))
    imap_ok = Column(Boolean, default=True)
    pop3_ok = Column(Boolean, default=True)
    is_master_user = Column(Boolean, default=False)
    is_public_user = Column(Boolean, default=False)
    is_archive_user = Column(Boolean, default=False)
    external_auth = Column(Boolean, default=False)

    domain = relation('cmDomain', backref=backref('allusers', order_by=username))
    groups = relation('cmGroup', secondary="pd_usergroup")

    @classmethod
    def sanitizeLatin1(self, string):
            string=string.replace('à', 'a')
            string=string.replace('è', 'e')
            string=string.replace('é', 'e')
            string=string.replace('ì', 'i')
            string=string.replace('ò', 'o')
            string=string.replace('ù', 'u')
            string=re.sub('[^a-z0-9-_]', '', string.lower())
            return string

    def __repr__(self):
        return r"<%s(username=%s)>" % (self.__class__.__name__,
                self.username)

    def __str__(self):
        return r"%s" % self.fullusername

  #   def __unicode__(self):
        # return u"%s %s" % (self.name, self.surname)

    def __init__(self, username='', password='', domain=None,
            schema='PLAIN',
            uid=None,
            external=False,
            name='',
            surname=''):
        """
        cmUser(username='xxxx', password='xxxx',domain=cmDomain)
        """

        if external:
            if not uid:
                raise IncorrectUserParams('uid MUST be specified for External Users')
            else:
                self.uid=uid
        else:
            if uid:
                self.uid=uid
            else:
                maxUid=meta.Session.query(sql_max(cmUser.uid)).first()[0]
                c=meta.Session.query(cmConfiguration).first()
                if maxUid < c.base_uid: maxUid=c.base_uid
                self.uid=maxUid+1


        self.username=username
        self.setPassword(password, schema)
        self.external_auth=external
        self.setFullname(name, surname)

        if domain:
            if domain.isPrimary():
                self.domain_id=domain.id
            else:
                self.domain_id=domain.primary_domain_id


    def setPassword(self, password, schema='PLAIN'):
        pwd=Password(password, schema)
        self.password=pwd.password
        self.schema=pwd.schema

    @property
    def special(self):
        return (self.is_master_user or self.is_public_user or self.is_archive_user)

    @property
    def sharing(self):
        return (self.is_public_user or self.is_archive_user)

    @property
    def fullusername(self):
        return "%s@%s" % (self.username, self.domain)

    @property
    def passwordwithschema(self):
        return "{%s}%s" % (self.schema, self.password)

    @property
    def quota(self):
        # meta.Session.rollback()
        return meta.Session.query(cmQuota).filter(cmQuota.username==self.fullusername).first()

    @property
    def home(self):
        return '%s/%s/%s' % (self.domain.base_dir, self.domain.name, self.username)

    @property
    def nameSurname(self):
        return "%s %s" % (self.name, self.surname)


    def setFullname(self, name, surname):
        self.setName(name)
        self.setSurname(surname)

    def setName(self, name):
        self.name=name.strip().title()

    def setSurname(self, surname):
        self.surname=surname.strip().title()

    def combineNameSurname(self):
        if not (self.name and self.surname):
            return []
        patterns=[
                '{name}.{surname}',
                '{name[0]}.{surname}',
                '{surname}',
                '{name[0]}{surname[0]}'
                ]

        parts ={'name': cmUser.sanitizeLatin1(self.name),
                'surname': cmUser.sanitizeLatin1(self.surname)}

        return [c.format(**parts) for c in patterns]

    def generateDefaultAliases(self, onlyPrimaryDomain=True):
        for couple in self.combineNameSurname():
            if not meta.Session.query(cmAlias).filter(cmAlias.left_part == couple).filter(cmAlias.domain == self.domain).first():
                newAlias=cmAlias(left_part=couple, domain=self.domain)
                newAlias.destinations.append(cmDestination(user=self))
                self.aliases.append(newAlias)

class cmGroup(Base, qbClass):
    __tablename__ = 'pd_groups'
    __table_args__  = ( UniqueConstraint("groupname", "domain_id"), {} )

    id = Column(Integer, Sequence('groups_id_seq'), primary_key=True)
    gid = Column(Integer, default=10000, nullable=False)
    groupname = Column(String(1024), nullable=False)
    domain_id = Column(Integer, ForeignKey('pd_domains.id'), nullable=False)


    domain = relation('cmDomain', backref=backref('groups', order_by=groupname))
    users = relation('cmUser', secondary="pd_usergroup")

    def __init__(self, groupname='', domain=None, gid=None):
        if gid:
            self.gid=gid
        else:
            maxGid=meta.Session.query(sql_max(cmGroup.gid)).first()[0]
            c=meta.Session.query(cmConfiguration).first()
            if maxGid < c.base_uid: maxGid=c.base_uid
            self.gid=maxGid+1

        self.groupname=groupname
        self.domain=domain

    @property
    def fullgroupname(self):
        return "%s@%s" % (self.groupname, self.domain)

    def __repr__(self):
        return r"<%s(group='%s', gid=%s, domain='%s')>" % (self.__class__.__name__,
                self.groupname,
                self.gid,
                self.domain)

    def __str__(self):
        return r"%s" % self.fullgroupname



class cmUserGroup(Base, qbClass):
    __tablename__ = 'pd_usergroup'

    user_id = Column(Integer, ForeignKey('pd_users.id'), primary_key=True)
    group_id = Column(Integer, ForeignKey('pd_groups.id'), primary_key=True)

    user = relation('cmUser')
    group = relation('cmGroup')

    def __repr__(self):
        return r"<%s(user='%s' [%s], group='%s' [%s])>" % (self.__class__.__name__,
                self.user, self.user.id,
                self.group, self.group.id)

class cmDomain(Base, qbClass):
    __tablename__ = 'pd_domains'

    id = Column(Integer, Sequence('domains_id_seq'), primary_key=True)
    primary_domain_id = Column(Integer, ForeignKey('pd_domains.id'), nullable=True)
    name = Column(String(1024), nullable=False)
    transport = Column(String(255), nullable=True, default='dovecot:')
    backup_transport = Column(String(255), nullable=True, default=None)
    base_dir = Column(String(1024), nullable=False, default='/var/mail')
    base_uid = Column(Integer, nullable=False, default='10000')
    quota = Column(String(32), nullable=False, default='10G')
    publicns = Column(Boolean, default=False)
    archivens = Column(Boolean, default=False)

    primary_domain = relation('cmDomain', remote_side = 'cmDomain.id', backref='domain_aliases')

    def __init__(self, name='', primary_domain=None):
        self.name=name
        self.primary_domain=primary_domain
        if primary_domain:
            self.base_dir=''
            self.base_uid=0
            self.transport=None

    def __repr__(self):
        return r"<%s(name='%s')>" % (self.__class__.__name__,
                self.name)

    def __str__(self):
        return r"%s" % (self.name)

    @property
    def users(self):
        """
        All users except master/public/archive
        """
        return [u for u in self.allusers if not u.special]

    @property
    def listusers(self):
        """
        All users except public/archive
        """
        return [u for u in self.allusers if not u.sharing]

    def getUserById(self, id):
        array=[item for item in self.listusers if item.id == id]
        if len(array):
            return array[0]
        else:
            return None

    def getAliasById(self, id):
        array=[item for item in self.aliases if item.id == id]
        if len(array):
            return array[0]
        else:
            return None

    def getGroupById(self, id):
        array=[item for item in self.groups if item.id == id]
        if len(array):
            return array[0]
        else:
            return None

    def isPrimary(self):
        return (self.primary_domain_id == None)

    def domainQuota(self):
        quotab=quotam=0
        for u in self.users:
        # for u in [u for u in self.users if not u.special]:
            if u.quota:
                quotab+=u.quota.bytes
                quotam+=u.quota.messages
        return (quotab, quotam)

    def enablePublicNS(self):
        if not self.publicns:
            pubuser=cmUser('pubowner', '**NONE**', domain=self,
                    schema='NULL',
                    name='Public User',
                    surname=self.name)
            pubuser.is_public_user=True
            meta.Session.add(pubuser)
            try:
                self.publicns=True
                meta.Session.commit()
            except:
                meta.Session.rollback()

    def disablePublicNS(self):
        if self.publicns:
            self.publicns=False
            pubuser=[user for user in self.users if user.is_public_user==True][0]
            meta.Session.delete(pubuser)
            meta.Session.commit()

    def enableArchiveNS(self):
        if not self.archivens:
            arcuser=cmUser('arcowner', '**NONE**', domain=self,
                    schema='NULL',
                    name='Archive User',
                    surname=self.name,
                    is_archive_user=True)
            meta.Session.add(arcuser)
            try:
                self.archivens=True
                meta.Session.commit()
            except:
                meta.Session.rollback()

    def disableArchiveNS(self):
        if self.archivens:
            self.archivens=False
            arcuser=[user for user in self.users if user.is_archive_user==True][0]
            meta.Session.delete(arcuser)
            meta.Session.commit()

# New Aliases Structure

class cmAlias(Base):
    __tablename__ = 'pd_aliases'
    __table_args__  = ( UniqueConstraint("left_part", "domain_id"), {} )

    id = Column(Integer, Sequence('%s_seq_id' % __tablename__, optional=True), primary_key=True)
    left_part = Column(String(255), nullable=False)
    domain_id = Column(Integer, ForeignKey('pd_domains.id'), nullable=False)

    domain = relation('cmDomain', backref=backref('aliases', order_by=id))
    destinations = relation('cmDestination', backref=backref('alias'), cascade="all, delete, delete-orphan")

    def __init__(self, left_part, domain):
        self.left_part=left_part
        self.domain=domain

    @validates('domain')
    def validate_domain(self, key, domain):
        assert domain != None, 'domain MUST be valid'
        return domain

    @validates('left_part')
    def validate_left_part(self, key, left_part):
        # Lowercase left_part
        # left_part MUST NOT contain @
        assert '@' not in left_part, 'left_part MUST NOT contain @'
        return left_part.lower()

    @property
    def fullalias(self):
        return str(self)

    def destString(self, sep=','):
        return sep.join(map(str, self.destinations))

    def __str__(self):
        return "%s@%s" % (self.left_part, self.domain.name)

    def __repr__(self):
        return "<%s(%s@%s)>" % (self.__class__.__name__,
                self.left_part,
                self.domain)

class cmDestination(Base):
    __tablename__ = 'pd_destinations'
    __table_args__  = (
            UniqueConstraint("user_id", "alias_id"),
            UniqueConstraint("redirect_to", "alias_id"),
            {} )

    id = Column(Integer, Sequence('%s_seq_id' % __tablename__, optional=True), primary_key=True)
    alias_id = Column(Integer, ForeignKey('pd_aliases.id'))
    user_id = Column(Integer, ForeignKey('pd_users.id'))
    extension = Column(String(1024), nullable=True, default=None)
    redirect_to = Column(String(1024), nullable=True, default=None)

    user = relation('cmUser', backref=backref('aliasdestination', order_by=id))

    @validates(user)
    def validate_user(self, key, user):
        # alias=meta.Session.query(cmAlias).get(self.alias_id)
        assert user.domain_id!=self.alias.domain_id, 'cross domain aliases are not implemented'

    @validates(user_id)
    def validate_user_id(self, key, user_id):
        user=meta.Session.query(cmUser).get(user_id)
        # alias=meta.Session.query(cmAlias2).get(self.alias_id)
        assert user.domain_id!=self.alias.domain_id, 'cross domain aliases are not implemented'

    @property
    def shortdestination(self):
        if self.user:
            if self.extension:
                return "%s+%s" % (self.user.username, self.extension)
            else:
                return self.user.username
        else:
            return self.redirect_to

    @property
    def destination(self):
        return '%s' % self.__str__()

    def __str__(self):
        if self.user:
            if self.user.is_public_user:
                return "#Public/%s" % self.extension
            if self.extension:
                return "%s+%s@%s" % (self.user.username, self.extension, self.user.domain)
            else:
                return self.user.fullusername
        else:
            return self.redirect_to

    def __repr__(self):
        return "<%s(%s -> %s)>" % (self.__class__.__name__,
                self.alias,
                self.destination)

class cmQuota(Base, qbClass):
    __tablename__ = 'pd_quotas'

    username = Column(String(255), primary_key=True, nullable=False)
    bytes = Column(BigInteger, default=0)
    messages = Column(Integer, default=0)

    def __repr__(self):
        return r"<%s(username='%s', bytes=%s, messages=%s)>" % (self.__class__.__name__,
                self.username, self.bytes, self.messages)


    @property
    def shortUsername(self):
        return self.username.split('@')[0]
    @property
    def domain(self):
        return self.username.split('@')[1]

    @property
    def user(self):
        dom=meta.Session.query(cmDomain).filter(cmDomain.name==self.shortUsername).first()
        user=meta.Session.query(cmUser).filter(cmUser.username==self.domain).filter(cmUser.domain==dom).first()
        return user

class cmConfiguration(Base, qbClass):
    __tablename__ = 'pd_configuration'

    key = Column(Integer, primary_key=True, default=0)
    base_uid = Column(Integer, default=10000)
    ldap_url = Column(String(255), default='ldap://ldap')
    admin_dn = Column(String(255))
    secret = Column(String(255))
    base_dn = Column(String(255), default='cn=users,', nullable=False)
    filter = Column(String(255), default='(objectClass=posixAccount)')
    login_field = Column(String(255), default='uid')
    uid_field = Column(String(255), default='uidNumber')
    name_field = Column(String(255), default='givenName')
    surname_field = Column(String(255), default='sn')
    email_field = Column(String(255), default='mail')
    group_dn = Column(String(255), default='cn=groups,')
    group_filter = Column(String(255), default='(objectClass=posixGroup)')

    def __repr__(self):
        return r'<%s>' % self.__class__.__name__

    @property
    def attrs(self):
        return [ self.login_field,
                self.uid_field,
                self.name_field,
                self.surname_field,
                self.email_field ]

class cmVersion(Base, qbClass):
    """
    Schema and software version
    """
    __tablename__ = 'pd_version'

    id = Column(Integer, Sequence('version_id_seq'), primary_key=True)
    schema_version = Column(Integer, default=1, nullable=False)
    sw_version = Column(Float, default=1.0, nullable=False)


